package server.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import server.control.Control;
import server.jsons.Daily;
import server.jsons.Entry;
import server.jsons.User;

public class EntriesInteraction {
	
	private static Logger logger = Logger.getLogger(EntriesInteraction.class.getName());

	public static List<Entry> obtainNewEntrySet(User u, String when){
		
		Connection con = ConnectionPool.getInstance().getConnection();
		PreparedStatement stmt = null;
		ResultSet res = null;
		String query = "SELECT * FROM CATEGORY WHERE category_uid = \"" + u.getUserId() + "\";";
		ArrayList<Entry> entries = new ArrayList<Entry>();
		
		try{
			stmt = con.prepareStatement(query);
			res = stmt.executeQuery();
			
			while(res.next()){
				
				Entry item = new Entry();
				//item.setId("-1");
				item.setCid(res.getString("cid"));
				item.setMaximum(res.getFloat("maximum"));
				item.setMinimum(res.getFloat("minimum"));
				item.setName(res.getString("name"));
				item.setStep(res.getFloat("step"));
				item.setUnity(res.getString("unity"));
				item.setWeight(res.getFloat("weight"));
				if(when.equals(Control.TODAY)){
					item.setDate(Control.getInstance().getDateToday());
				}else if(when.equals(Control.YESTERDAY)){
					item.setDate(Control.getInstance().getDateYesterday());
				}
				item.setValue(0);
				
				entries.add(item);
			}
			return entries;
		}catch(SQLException e){
			logger.warn(e);
			return entries;
		}finally{
			try{
				if(stmt != null)
					stmt.close();
				if(con != null)
					con.close();
			}catch(SQLException e){
				logger.warn("Couldn't close SQLInteraction. Exception: " + e);
			}
		}
	}

	public static String saveEntry(Entry entry) {
		Connection con = ConnectionPool.getInstance().getConnection();
		PreparedStatement stmt = null;
		ResultSet res = null;
		String query = "INSERT INTO ENTRIES(date, weight, maximum, minimum, step, unity, value, entries_cid) "
				+ " VALUES('" + entry.getDate() + "', '" + entry.getWeight() + "', '" + entry.getMaximum() + "', '"
						+ entry.getMinimum() + "', '" + entry.getStep() + "', '" + entry.getUnity() + "', '" + entry.getValue()
						+ "', '" + entry.getCid() + "');";
		
		logger.debug(query);
		try{
			stmt = con.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);
			stmt.executeUpdate();
			res = stmt.getGeneratedKeys();
			res.next();
		
			return res.getString(1);

		}catch(SQLException e){
			logger.warn(e);
			return "0";
		}finally{
			try{
				if(stmt != null)
					stmt.close();
				if(con != null)
					con.close();
			}catch(SQLException e){
				logger.warn("Couldn't close SQLInteraction. Exception: " + e);
				return "0";
			}
		}
		
	}
	
	public static String saveDaily(Daily daily, User u) {
		Connection con = ConnectionPool.getInstance().getConnection();
		PreparedStatement stmt = null;
		ResultSet res = null;

		String query = "INSERT INTO DAILY(date, bedtime, waketime, satisfaction, motivation, daily_uid)"
				+ " VALUES('" + daily.getDate() + "', '" + new Timestamp(daily.getSleepdate().getTime()) + "', '" 
				+ new Timestamp(daily.getWakedate().getTime()) + "', '"
						+ daily.getSatisfaction() + "', '" + daily.getMotivation() + "', '" + u.getUserId() + "');";
		
		logger.debug(query);
		try{
			stmt = con.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);
			stmt.executeUpdate();
			res = stmt.getGeneratedKeys();
			res.next();
		
			return res.getString(1);

		}catch(SQLException e){
			logger.warn(e);
			return "0";
		}finally{
			try{
				if(stmt != null)
					stmt.close();
				if(con != null)
					con.close();
			}catch(SQLException e){
				logger.warn("Couldn't close SQLInteraction. Exception: " + e);
				return "0";
			}
		}
		
	}
	
	
}
